﻿CREATE OR REPLACE FUNCTION crearcuenta(integer, character varying)
  RETURNS void AS
$BODY$
    INSERT INTO cliente VALUES ($1, $2);
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION crearcuenta(integer, character varying) OWNER TO postgres;



CREATE OR REPLACE FUNCTION getcargas(integer)
  RETURNS integer AS
$BODY$
	SELECT idCarga FROM relacion WHERE idcliente = $1;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION getcargas(integer) OWNER TO postgres;


CREATE OR REPLACE FUNCTION registrarcargas(integer, timestamp without time zone, numeric, boolean, character varying, boolean, integer, integer)
  RETURNS void AS
$BODY$
	INSERT INTO carga VALUES ($1, $2, null, $3, $4, $5, null,$6,$7,$8);
	INSERT INTO relacion VALUES ($1, $7, $8);	
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION registrarcargas(integer, timestamp without time zone, numeric, boolean, character varying, boolean, integer, integer) OWNER TO postgres;


CREATE OR REPLACE FUNCTION reporte1(date, date)
  RETURNS SETOF record AS
$BODY$
declare
registro record;
begin
	FOR registro in SELECT Carga.idcarga,Cliente.idcliente, Cliente.nombre,Carga.fechaIngreso,Carga.fechaSalida,Carga.refrigerada, Carga.peso, Almacen.idalmacen, Rack.fila, Rack.columna, Rack.idrack 
	FROM Carga, Cliente, Rack, Almacen
	WHERE Carga.idcliente = Cliente.idCLiente and Carga.idRack = Rack.idRack
	and Rack.idalmacen = Almacen.idalmacen
	and (date(Carga.fechaIngreso) >= $1 and date(Carga.fechaIngreso)<=$2)
	ORDER BY Carga.fechaIngreso ASC
	Loop 
		Return Next registro;
	end Loop;
	Return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte1(date, date) OWNER TO postgres;



Select * from reporte1(cast('2011-01-01' as date), cast('2011-12-01' as date)) as (IdCarga integer, IdCliente integer, NombreCliente varchar(200), FechaIngreso timestamp, FechaSalida timestamp, Refrigerada boolean, PesoCarga numeric, IdAlmacen integer, Fila integer, Columna integer, IdRack integer)

CREATE OR REPLACE FUNCTION reporte2()
  RETURNS SETOF record AS
$BODY$
declare
registro record;
begin
	FOR registro IN SELECT Almacen.idalmacen, Almacen.estado, Rack.idrack, Rack.fila, Rack.columna, Carga.peso
	FROM Almacen, Rack, Carga
	WHERE Rack.idalmacen = Almacen.idalmacen and Carga.idrack = Rack.idrack
	ORDER BY Rack.idalmacen, Rack.fila, Rack.columna, rack.idrack
	LOOP
	  RETURN NEXT registro;
	END LOOP;
	RETURN;  
	end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte2() OWNER TO postgres;

Select * from reporte2() as(idalmacen integer, estado boolean, idrack int, fila int, columna int, peso numeric);

CREATE OR REPLACE FUNCTION reporte4(date, date)
  RETURNS SETOF record AS
$BODY$
declare
registro record;
begin
	FOR registro in SELECT Carga.idcarga, Carga.fechaIngreso,Carga.fechaSalida, Carga.peso, Almacen.idalmacen, Rack.fila, Rack.columna, Rack.idrack 
	FROM Carga, Rack, Almacen
	WHERE Carga.idRack = Rack.idRack and Almacen.idalmacen = Rack.idalmacen
	and (date(Carga.fechaSalida) >= $1 and date(Carga.fechaSalida)<=$2)
	ORDER BY Carga.fechaSalida ASC
	Loop 
		Return Next registro;
	end Loop;
	Return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte4(date, date) OWNER TO postgres;

Select * from reporte4(cast('2011-01-01' as date), cast('2011-12-01' as date)) as (IdCarga integer, FechaIngreso timestamp, FechaSalida timestamp, PesoCarga numeric, IdAlmacen integer, Fila integer, Columna integer, IdRack integer)


CREATE OR REPLACE FUNCTION reporte5(int) RETURNS SETOF record AS $$
declare
  registro record;
begin
  FOR registro IN SELECT to_char(fechaIngreso, 'month'),
  SUM(Carga.peso),
  SUM(CASE WHEN Carga.estado = true THEN peso ELSE 0 END),
  movimiento,
  SUM(montoc)
  FROM Carga
  WHERE EXTRACT (YEAR FROM (cast(fechaIngreso as date))) = $1 and EXTRACT (YEAR FROM (cast (fechaSalida as date)))=$1
  GROUP BY to_char(fechaIngreso, 'month'), movimiento, montoc
  LOOP
    RETURN NEXT registro;
  END LOOP;
  RETURN;
END;
$$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte5(int) OWNER TO postgres;


Select * FROM reporte5(2011) as (mes text, cargatotalingresada numeric, ctotalegresada numeric, movimiento varchar, montoc numeric);